Migration Generation

Migration generation in Aunsight involves specifying either an Atlas record or a dataset schema for each table, describing the desired final state. Aunsight compares the current table schema, if available, and determines necessary actions such as creating tables, adding/removing/adjusting columns, and even renaming. It can perform these actions for multiple tables and provides options to drop all tables or unmentioned ones at the start of the plan. The Atlas record used for reference doesn't require data; its purpose is to expedite schema specification.

Migrations can be produced and executed through the Aunsight UI, toolbelt, or API. While all options are available, the Aunsight UI is recommended, especially for manual tasks.

Instructions for filling out the JSON object

Instructions:

• Leave the drop_others field as false if you don't want to drop any existing tables not mentioned in the tables argument. Set it to true if you want to drop them.
• Leave the drop_all field as false if you don't want to drop all tables first. Set it to true if you want to drop them.

Tables:

• Fill in the id field with a unique identifier for each table within the datamart.
• Provide a human-friendly name for the table.
• Optionally, include a description to describe the table's purpose.
• Set drop to true if you want to drop the table. If not, leave it as false.
• If you want to rename the table, specify the rename field with the current table name and update id with your desired new table name.
• Assign the record field with the Atlas record ID to derive the schema and metadata from.

  Note: If both record and schema are provided, they will be merged. Adding extra metadata to a record-derived schema is recommended.

• If you want to provide a custom JSON schema for the table, include it in the schema field. It should be a valid JSON object.
• Define the properties field as a JSON object containing the field definitions for the table. Ensure each field has at least a type attribute.
• List the keys of the properties object in the desired field order using the propertiesOrder array.

Note: Each table/view must have a schema specified either via record, schema, or both

Views:

• Follow the same instructions as for tables for the id, name, description, and schema fields.
• Specify the SQL query that will be used to create the view in the query field.

  Note: The schema must accurately match the query result structure.

• Provide the field definitions for the view in the properties field as a JSON object. Each field must have at least a type attribute.
• List the keys of the properties object in the desired field order using the propertiesOrder array.
• Remember to fill in the necessary values according to your specific requirements for each table and view in the JSON object.

{
  "drop_others": false,
  "drop_all": false,
  "tables": [
    {
      "id": "table1",
      "name": "Table 1",
      "description": "This is Table 1",
      "drop": false,
      "rename": "",
      "record": "record1",
      "schema": {},
      "properties": {},
      "propertiesOrder": []
    },
    {
      "id": "table2",
      "name": "Table 2",
      "description": "This is Table 2",
      "drop": false,
      "rename": "",
      "record": "record2",
      "schema": {},
      "properties": {},
      "propertiesOrder": []
    }
  ],
  "views": [
    {
      "id": "view1",
      "name": "View 1",
      "description": "This is View 1",
      "query": "SELECT * FROM table1",
      "schema": {},
      "properties": {},
      "propertiesOrder": []
    },
    {
      "id": "view2",
      "name": "View 2",
      "description": "This is View 2",
      "query": "SELECT * FROM table2",
      "schema": {},
      "properties": {},
      "propertiesOrder": []
    }
  ]
}